Fork me on GitHub

MySQL 高级提升(四)

发表于 | 分类于 ， | 热度: °C
字数统计: 2,058 | 阅读时长 ≈ 7

前言：

  1. 视图的作用、特点以及使用；
  2. 事务的 ACID 特性以及使用；
  3. 面试题：对索引的认识以及使用；
  4. 数据库的设计：E-R 模型、三范式。

一、视图

1. 掌握视图的作用、特点以及如何使用

特点： 
    视图是对若干张基本表的引用，一张虚表，查询语句执行的结果
    不存储具体的数据（基本表数据发生了改变，视图也会跟着改变） 

使用：
    1. create view 视图名称 as select ...
    2. select * from 视图
    3. drop view 视图名称
    
作用：视图的优缺点（面试：谈一谈对视图的认识）
	
	a>优点:
		1. 简化查询操作
			对于复杂的查询，往往是有多个数据表进行关联查询而得到,对于这个复杂的查询结果保存为一个视图，便于下一次查询时简化查询操作
			
		2. 逻辑上的独立性，屏蔽了真实表结构更改带来的影响
			视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
		
		3. 视图能够对机密数据提供一定安全保护
			因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户
			
	b> 不足：
	    1. 性能差
			对视图(select)的查询最终转换为对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，也会花费一定时间
				
		2. 额外增加数据库的复杂度

二、事务

在Python中，打开一个连接，默认会开启事务，针对增删改，需要事务的提交 conn.commit()

遇到的问题？
	银行转账分为2部分
		1. A账号转出1000元
		2. B账号转入1000元
	两部分操作是不可分割的，要么一起成功，要么一起失败
解决方案： 使用事务

1. 事务是什么

	所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的执行单位
	
2. 事务的ACID特性 (面试题：事务的ACID是什么)
   
	a> 原子性(Atomic)： 语句不可分割，要么同时执行，要么同时不执行
	b> 一致性(Consistency): 数据库总是从一个一致性的状态转换到另一个一致性的状态
	c> 隔离性(Isolation)：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的
	d> 持久性(Durability)
	    一旦事务提交，则其所做的修改会永久保存到数据库
   
3. mysql事务的使用

	1. 开启事务命令
		# 开启事务
		# start transaction;
		begin;  
	2. 提交事务命令
	    commit；
	3. 事务回滚命令
		rollback;

4. 事务的提示：

	1. innodb引擎支持事务(默认的引擎)，MyISAM不支持事务
	2. 使用终端操作数据库(也就是mysql的客户端)的时候 也是默认开始事物的，只是在回车确认操作的时候 终端会默认执行commit 所以我们不需要手动commit。但假如手动调用begin时，就需要手动调用commit提交事务
	3. 使用python操作数据库的时候 默认开启事务的 
	4. 但是python对数据库进行增删改的时候 需要手动commit

三、索引

遇到问题1：
    如何快速查字典的单词，快速找一本书中的某章节的具体内容？
遇到问题2：
    当数据库中数据量很大时，查找数据会变得很慢，如何优化查询？
解决方案：使用索引
		
1. 什么是索引
	索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
	索引就好比是一本书前面的目录，能加快数据库的查询速度
	
2. 索引原理(了解)
    思想： 不断缩小查找范围
    内部具体实现方案： B+Tree实现
        数据分析： 数据结构
        线性表，二叉树，三叉数， 队列， 栈 

3. 索引的使用
	a> 查看索引	
		show index from 表名;
		show index from goods;

	b> 创建索引
		create index 索引名称 on 表名(字段名称(长度))			
		create index name_index on goods(name(150));

	c> 删除索引
		drop index 索引名称 on 表名;
		drop index name_index on goods;
	
4. 索引优化查询测试
	1. 创建表 t_news(新闻)表
	   create table t_news(title varchar(10));
	2. 通过python程序向t_news表添加10万条记录
	3. 测试有无索引的查询效率
		set profiling=1; # 开启时间检测
		select * from t_news where title='ha-80000'; # 建立索引之前执行查询
		create index title_index on t_news(title(10)); # 创建索引之后执行查询
		select * from t_news where title='ha-80000'; 
		show profiles; # 显示执行简表
	
5. 索引注意点(面试题:谈谈你对索引的认识):
    1. 索引最主要解决的问题:当数据量较大时,且这些数据不需要经常修改,使用索引来加快查询速度
	2. 对于比较小的表，查询开销不会很大，也没有必要建立另外的索引
	3. 建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件
	4. 对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了 
	5. 建立索引会占用磁盘空间

四、数据库的设计

E-R模型：实体关系模型
    1. 一对一
    2. 一对多
    3. 多对一
    4. 多对多
    
三范式
    1NF： 列不能再分割
    2NF： 基于1NF，有主键，非主键要依赖主键
    3NF ：基于2NF，不能传递依赖主键



1. E-R模型(Entity-Relationship Model)

	关系型数据库是建立在实体关系模型(E-R模型)基础之上,再开展数据库的分析与设计
        基于面向对象思想的分析与设计
        
        面向对象的分析与设计：
            1. 同学  2. 老师  3. 班级  4. 教室
            实体-关系模型 

	在项目开发中，一般会先设计好数据库，数据库开发一般是项目开发的第一步
	a> 实体(Entry)
		实体设计就好比定义一个类一样，指定从哪些方面来描述对象，一个实体可以转换为数据库中的一个表

	b> 关系(Relationship)
		描述两个实体之间的对应规则
		1. 一对一：
			一个萝卜一个坑
			学生与指纹
			员工和工资卡

		2. 一对多
			班级与学生
			客户与订单
			部门与员工
			
		3. 多对一
			学生与班级
			员工与部门
			
		4. 多对多关系
		  学生与社团
		  运动员与比赛项目

2. 三范式:

	为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式
	数据库的范式越高，则数据冗余越少，但是会导致查询效率降低，一般数据库设计满足第三范式，就很好了
	
	a> 第一范式 1NF 
		强调的是列的原子性，即列不能够再分成其他几列 
		
	b> 第二范式
		首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键
		   依据主键能够推导出其他字段的信息
	
	c> 第三范式
		首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况
-------------本文结束感谢您的阅读-------------

本文标题:MySQL 高级提升(四)

文章作者:曹永林

发布时间:2018年07月18日 - 04:07

最后更新:2018年07月28日 - 09:07

原始链接:http://jovelin.cn/2018/07/18/MySQL 高级提升(四)/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。